package com.example.lsrsbackend.mapper;


import com.example.lsrsbackend.model.Reservation;
import com.example.lsrsbackend.model.Seat;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.*;

import java.sql.Time;
import java.util.Date;
import java.util.List;

@Mapper
public interface SeatMapper {

    // 基本CRUD操作
    @Select("SELECT * FROM seats WHERE id = #{id}")
    Seat selectById(Long id);

    @Select("SELECT * FROM seats ")
    List<Seat> selectAll();

    @Insert("INSERT INTO seats (floor_area_id, seat_number, seat_type, base_status, created_at, updated_at) " +
            "VALUES (#{floorAreaId}, #{seatNumber}, #{seatType}, #{baseStatus}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertSeat(Seat seat);

    @Delete("DELETE FROM seats WHERE id = #{id}")
    int deleteSeat(Long id);

    @Select("SELECT COUNT(*) FROM seats WHERE floor_area_id = #{floorAreaId} AND seat_number = #{seatNumber} AND id != #{excludeId}")
    int countByFloorAndSeatNumberExcludingId(@Param("floorAreaId") Long floorAreaId,
                                             @Param("seatNumber") String seatNumber,
                                             @Param("excludeId") Long excludeId);

    @Select("SELECT COUNT(*) FROM seats WHERE floor_area_id = #{floorAreaId} AND seat_number = #{seatNumber}")
    int countByFloorAndSeatNumber(@Param("floorAreaId") Long floorAreaId,
                                  @Param("seatNumber") String seatNumber);
    @Update("UPDATE seats SET floor_area_id = #{floorAreaId}, seat_number = #{seatNumber}, " +
            "seat_type = #{seatType}, base_status = #{baseStatus}, updated_at = NOW() " +
            "WHERE id = #{id}")
    int updateSeat(Seat seat);

    @Select("SELECT * FROM seats WHERE floor_area_id = #{floorAreaId} ")
    List<Seat> selectByFloorAreaId(Long floorAreaId);

    @Select("SELECT id, floor_area_id, seat_number, seat_type, base_status, created_at, updated_at FROM seats ORDER BY floor_area_id, seat_number")
    List<Seat> findAllSeats();

    @Select("SELECT id, floor_area_id, seat_number, seat_type, base_status, created_at, updated_at FROM seats WHERE id = #{id}")
    Seat findSeatById(Long id);

    @Select("SELECT id, floor_area_id, seat_number, seat_type, base_status, created_at, updated_at FROM seats WHERE floor_area_id = #{floorAreaId} ORDER BY seat_number")
    List<Seat> findSeatsByFloorAreaId(Long floorAreaId);

    @Select("SELECT id, floor_area_id, seat_number, seat_type, base_status, created_at, updated_at FROM seats WHERE base_status = 'ACTIVE'")
    List<Seat> findActiveSeats();

    @Update("UPDATE seats SET base_status = #{baseStatus}, updated_at = NOW() WHERE id = #{id}")
    int updateSeatStatus(@Param("id") Long id, @Param("baseStatus") Seat.BaseStatus baseStatus);

    // ========== 预约相关操作 ==========

    @Select("SELECT id, seat_id, user_id, date, start_time, end_time, duration_hours, status, actual_checkin_time, actual_checkout_time, extend_count, created_at, updated_at FROM reservations WHERE id = #{id}")
    Reservation findReservationById(Long id);

    @Select("SELECT id, seat_id, user_id, date, start_time, end_time, duration_hours, status, actual_checkin_time, actual_checkout_time, extend_count, created_at, updated_at FROM reservations WHERE user_id = #{userId} AND date >= #{startDate} ORDER BY date DESC, start_time DESC")
    List<Reservation> findReservationsByUserAndDate(@Param("userId") Long userId, @Param("startDate") Date startDate);

    @Select("SELECT id, seat_id, user_id, date, start_time, end_time, duration_hours, status, actual_checkin_time, actual_checkout_time, extend_count, created_at, updated_at FROM reservations WHERE seat_id = #{seatId} AND date = #{date} AND status IN ('RESERVATED', 'CHECKED_IN')")
    List<Reservation> findActiveReservationsBySeatAndDate(@Param("seatId") Long seatId, @Param("date") Date date);

    @Insert("INSERT INTO reservations (seat_id, user_id, date, start_time, end_time, duration_hours, status, created_at, updated_at) VALUES (#{seatId}, #{userId}, #{date}, #{startTime}, #{endTime}, #{durationHours}, #{status}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertReservation(Reservation reservation);

    @Update("UPDATE reservations SET status = #{status}, actual_checkin_time = #{actualCheckinTime}, actual_checkout_time = #{actualCheckoutTime}, extend_count = #{extendCount}, updated_at = NOW() WHERE id = #{id}")
    int updateReservation(Reservation reservation);

    @Select("SELECT COUNT(*) FROM reservations WHERE user_id = #{userId} AND date = #{date} AND status IN ('RESERVATED', 'CHECKED_IN')")
    int countUserReservationsByDate(@Param("userId") Long userId, @Param("date") Date date);

    @Select("SELECT COUNT(*) FROM reservations WHERE seat_id = #{seatId} AND date = #{date} AND status IN ('RESERVATED', 'CHECKED_IN') AND ((start_time < #{endTime} AND end_time > #{startTime}))")
    int countTimeConflict(@Param("seatId") Long seatId, @Param("date") Date date, @Param("startTime") Time startTime, @Param("endTime") Time endTime);

    @Select("SELECT * FROM reservations WHERE status = 'RESERVATED' AND date = #{date} AND start_time <= #{currentTime}")
    List<Reservation> findExpiredReservations(@Param("date") Date date, @Param("currentTime") Time currentTime);

    @Update("UPDATE reservations SET status = 'EXPIRED', updated_at = NOW() WHERE id = #{id}")
    int updateReservationToExpired(Long id);

    @Select("SELECT * FROM reservations WHERE status = 'CHECKED_IN' AND date = #{date} AND end_time <= #{currentTime}")
    List<Reservation> findCompletedReservations(@Param("date") Date date, @Param("currentTime") Time currentTime);

    @Update("UPDATE reservations SET status = 'COMPLETED', updated_at = NOW() WHERE id = #{id}")
    int updateReservationToCompleted(Long id);
}
